using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using Nvigorate.Common;
using Nvigorate.Data.Query;
using Nvigorate.Extensions;

namespace Nvigorate.Data
{
    public class MSSQL9Visitor : IQueryVisitor
    {
        protected static IDatabaseProvider _provider = new MSSQL9Provider();

        public IDatabaseProvider Provider
        {
            get { return _provider; }
            set { _provider = value; }
        }

        #region Dictionary

        private readonly Dictionary<Type, Func<IQueryObject, MSSQL9Visitor, string>> _visitors
            = new Dictionary<Type, Func<IQueryObject, MSSQL9Visitor, string>>()
                  {
                      { typeof (AssignmentColumn), ProcessAssignmentColumn },
                      { typeof (Between), ProcessBetween },
                      { typeof (Column), ProcessColumn },
                      { typeof (CriteriaClause), ProcessCriteria },
                      { typeof (Delete), ProcessDelete },
                      { typeof (Equal), ProcessEqual },
                      { typeof (Function), ProcessFunction },
                      { typeof (Greater), ProcessGreater },
                      { typeof (GreaterOrEqual), ProcessGreaterOrEqual },
                      { typeof (Group), ProcessGroup },
                      { typeof (IfBlock), ProcessIfBlock },
                      { typeof (In), ProcessIn },
                      { typeof (Insert), ProcessInsert },
                      { typeof (InsertColumn), ProcessInsertColumn },
                      { typeof (Less), ProcessLess },
                      { typeof (LessOrEqual), ProcessLessOrEqual},
                      { typeof (Like), ProcessLike },
                      { typeof (Literal), ProcessLiteral },
                      { typeof (NotEqual), ProcessNotEqual },
                      { typeof (Order), ProcessOrder },
                      { typeof (OrderColumn), ProcessOrderColumn },
                      { typeof (Output), ProcessOutput },
                      { typeof (Page), ProcessPage },
                      { typeof (Property), ProcessProperty },
                      { typeof (QueryList), ProcessQueryList },
                      { typeof (RaiseError), ProcessRaiseError },
                      { typeof (Reference), ProcessReference },
                      { typeof (SchemaColumn), ProcessSchemaColumn },
                      { typeof (SearchCriteria), ProcessSearchCriteria },
                      { typeof (Select), ProcessSelect },
                      { typeof (Table), ProcessTable },
                      { typeof (TableVariable), ProcessTableVariable },
                      { typeof (Top), ProcessTop },
                      { typeof (Update), ProcessUpdate },
                      { typeof (Variable), ProcessVariable },
                      { typeof (Where), ProcessWhere },
                  };

        private readonly Dictionary<JoinType, string> _joinNames = new Dictionary<JoinType, string>()
                                                                       {
                                                                           {JoinType.Inner, "JOIN"},
                                                                           {JoinType.Left, "LEFT JOIN"},
                                                                           {JoinType.Right, "RIGHT JOIN"},
                                                                           {JoinType.FullOuter, "FULL OUTER JOIN"},
                                                                       };

        #endregion
        
        public string Translate(IQueryObject queryObject)
        {
            try
            {
                return _visitors[queryObject.GetType()](queryObject, this);
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                return "";
            }
        }

        protected string PrefixCriterion(ICriterion criterion, string representation)
        {
            if (criterion.Conjunction ==
                Conjunctions.None)
                return representation;

            return string.Format("{0} {1}", criterion.Conjunction == Conjunctions.And ? "AND" : "OR", representation);
        }

        protected Page GetPage(Select query)
        {
            return query.Qualifiers.OfType<Page>().FirstOrDefault();
        }

        // ((@PageNum - 1) * @PageSize + 1) AND (@PageNum * @PageSize)
        protected Select PageSelect(Select query, Page page)
        {
            var orderClause = GetQueryOrder(query);
            var rowNumber = (Reference) string.Format("ROW_NUMBER() OVER({0}) AS RowNumber", Translate(orderClause));

            //step 1 - create the select list for the outer select by using alias only
            var outerColumns = new QueryList().AddRange(
                query
                    .ColumnList
                    .Cast<Column>()
                    .Select(c => 
                            //Column.Named(string.Format("{0}.{1}", c.Owner, c.Name)))
                            Column.Named(string.IsNullOrEmpty(c.Alias) ? string.Format("{0}.{1}", c.Owner, c.Name) : c.Alias))
                    .Cast<IQueryObject>()
                );

            //step 2 - create the inner select list by aliasing the table/column combinations
            var innerColumns = new QueryList().AddRange(
                query
                    .ColumnList
                    .Cast<Column>()
                    .Select(c =>
                            //Column.Named(c.Name).OwnedBy(c.Owner).As(string.Format("{0}.{1}", c.Owner, c.Name)))
                            Column.Named(c.Name)
                                .OwnedBy(c.Owner)
                                .As(string.IsNullOrEmpty(c.Alias) ? string.Format("{0}.{1}", c.Owner, c.Name) : c.Alias)
                                .AliasOwner(c.OwnerAlias))
                    .Cast<IQueryObject>()
                ).Add(rowNumber); // adds the row count which is required for paging

            //step 3 - create an outer version of the order clause that uses aliases
            var outerOrderClause = Order.By();
            orderClause.ColumnList.Cast<OrderColumn>().ForEach(c =>
                {
                    var column = string.IsNullOrEmpty(c.Alias) ? string.Format("{0}.{1}", c.Owner, c.Name) : c.Alias;
                    if(c.Order == SortOrder.Descending)
                    {
                        outerOrderClause.Descending(column);
                    }
                    else
                    {
                        outerOrderClause.Ascending(column);
                    }
                });

            query.ColumnList.Clear();
            query.ColumnList.AddRange(innerColumns);
            query.Qualifiers.RemoveAll(q => q is Page);
            query.Qualifiers.RemoveAll(q => q is Order);
            return new Select(
                query.As(query.Table.Name + "1"),
                outerColumns,
                Where.Criteria(Criterion.Column("RowNumber", "")
                                   .Between(
                                   (Literal) ((page.Index - 1) * page.Size + 1))
                                   .And(
                                   (Literal) (page.Index * page.Size))),
                outerOrderClause);
        }

        protected Order GetQueryOrder(Select query)
        {
            var order = query.Qualifiers.OfType<Order>().FirstOrDefault();
            if (order == null || order.ColumnList.Count == 0)
            {
                var columns = query.ColumnList.OfType<Column>().Where(c => c.PrimaryKey);
                if (columns == null)
                {
                    var orderClause = Order.By();
                    foreach (var column in columns)
                    {
                        orderClause.Ascending(column);
                    }
                    order = orderClause;
                }
                else
                {
                    var first = query
                        .ColumnList
                        .OfType<Column>()
                        .First();
                    order = Order.By().Ascending(first);
                }
            }

            return order;
        }

        public string GetQueryObjectName<T>(QueryObject<T> queryObject) where T : QueryComponent
        {
            var builder = new DelimitedBuilder("");

            var ownerName = string.IsNullOrEmpty(queryObject.OwnerAlias) ? queryObject.Owner : queryObject.OwnerAlias;
            if(ownerName != "")
            {
                if (queryObject.Name != "*")
                {
                    builder.AppendFormat("[{0}].[{1}]", ownerName, queryObject.Name);
                }
                else
                {
                    builder.AppendFormat("{0}.*", ownerName);
                }
            }
            else
            {
                builder.AppendFormat(
                    queryObject.Name != "*" ? "[{0}]" : "{0}",
                    queryObject.Name);
            }

            var orderColumn = queryObject as OrderColumn;
            if(queryObject.Alias != "" && orderColumn == null)
            {
                builder.AppendFormat(" AS [{0}]", queryObject.Alias);
            }

            return builder.ToString();
        }

        #region Processor Methods

        protected static string ProcessAssignmentColumn(IQueryObject queryObject, MSSQL9Visitor visitor)
        {
            var column = queryObject as AssignmentColumn;
            return string.Format("{0} = {1}", visitor.GetQueryObjectName(column), visitor.Translate(column.Value));
        }

        protected static string ProcessBetween(IQueryObject queryObject, MSSQL9Visitor visitor)
        {
            var between = queryObject as Between;
            var result = string.Format(
                "{0} BETWEEN {1} AND {2}",
                visitor.Translate(between.Field),
                visitor.Translate(between.Lower),
                visitor.Translate(between.Upper));
            return visitor.PrefixCriterion(between, result);
        }

        protected static string ProcessColumn(IQueryObject queryObject, MSSQL9Visitor visitor)
        {
            var column = queryObject as Column;
            return visitor.GetQueryObjectName(column);
        }

        protected static string ProcessCriteria(IQueryObject queryObject, MSSQL9Visitor visitor)
        {
            var clause = queryObject as CriteriaClause;
            var builder = new DelimitedBuilder(" ");
            builder.Append("(");
            clause.Criteria.ForEach(c => builder.Append(visitor.Translate(c)));
            builder.Append(")");
            return builder.ToString();
        }

        protected static string ProcessDelete(IQueryObject queryObject, MSSQL9Visitor visitor)
        {
            var delete = queryObject as Delete;
            var builder = new DelimitedBuilder(" ");
            builder.AppendFormat("DELETE FROM {0} {1} {2}",
                                 visitor.Translate(delete.Table),
                                 delete.Output != null ?
                                    visitor.Translate(delete.Output) :
                                    "",
                                 visitor.Translate(delete.Qualifiers)
                );
            return builder.ToString();
        }

        protected static string ProcessEqual(IQueryObject queryObject, MSSQL9Visitor visitor)
        {
            var equal = queryObject as Equal;
            var result =
                equal.To as Reference != null ?
                    (equal.To as Reference).Value.ToString() == "NULL" ?
                        string.Format("{0} IS NULL", visitor.Translate(equal.Field)) :
                        string.Format("{0} = {1}", visitor.Translate(equal.Field), visitor.Translate(equal.To)) :
                    string.Format("{0} = {1}", visitor.Translate(equal.Field), visitor.Translate(equal.To));

            return visitor.PrefixCriterion(equal, result);
        }

        protected static string ProcessFunction(IQueryObject queryObject, MSSQL9Visitor visitor)
        {
            var function = queryObject as Function;
            var builder = new DelimitedBuilder(" ");
            if (function.Alias != "")
                builder.Append("(");

            if (function.Owner != "")
                builder.Append(function.Owner, "", ".", false);

            builder.AppendFormat(
                "{0}({1})",
                function.Name,
                DelimitedBuilder.Construct(function.ParameterList.Select(p => visitor.Translate(p)), ", "));

            if (function.Alias != "")
                builder.AppendFormat(") AS [{0}]", function.Alias.Replace("[","").Replace("]",""));

            return builder.ToString();
        }

        protected static string ProcessGreater(IQueryObject queryObject, MSSQL9Visitor visitor)
        {
            var greater = queryObject as Greater;
            var result = string.Format("{0} > {1}", visitor.Translate(greater.Field), visitor.Translate(greater.Than));
            return visitor.PrefixCriterion(greater, result);
        }

        protected static string ProcessGreaterOrEqual(IQueryObject queryObject, MSSQL9Visitor visitor)
        {
            var greater = queryObject as GreaterOrEqual;
            var result = string.Format("{0} >= {1}", visitor.Translate(greater.Field), visitor.Translate(greater.Than));
            return visitor.PrefixCriterion(greater, result);
        }

        protected static string ProcessGroup(IQueryObject queryObject, MSSQL9Visitor visitor)
        {
            var group = queryObject as Group;
            return string.Format("GROUP BY {0}",
                                 DelimitedBuilder.Construct(group.ColumnList.Select(c => visitor.Translate(c)), ", "));
        }

        protected static string ProcessIfBlock(IQueryObject queryObject, MSSQL9Visitor visitor)
        {
            var ifBlock = queryObject as IfBlock;

            var result = string.Format("IF {0}\r\n BEGIN\r\n{1}\r\n END",
                                       visitor.Translate(ifBlock.Criteria),
                                       visitor.Translate(ifBlock.ActionIfTrue));

            if(ifBlock.ActionIfFalse != null)
            {
                result += string.Format("ELSE\r\n BEGIN\r\n{0}\r\n END", visitor.Translate(ifBlock.ActionIfFalse));
            }
            return result;
        }

        protected static string ProcessIn(IQueryObject queryObject, MSSQL9Visitor visitor)
        {
            var inCriterion = queryObject as In;
            var result = string.Format("{0} IN ({1})", visitor.Translate(inCriterion.Field), visitor.Translate(inCriterion.Values));
            return visitor.PrefixCriterion(inCriterion, result);
        }

        protected static string ProcessInsert(IQueryObject queryObject, MSSQL9Visitor visitor)
        {
            var insert = queryObject as Insert;

            var outputClause = insert.Output == null ?
                                "" : visitor.Translate(insert.Output);

            if (insert.SelectQuery != null)
            {
                if (insert.ColumnList != null)
                {
                    return string.Format("\r\nINSERT {0} ({1}) {2}\r\n {3}",
                                         visitor.Translate(insert.Table),
                                         visitor.Translate(insert.ColumnList),
                                         outputClause,
                                         visitor.Translate(insert.SelectQuery));
                }
                else
                {
                    return string.Format("\r\nINSERT {0} {1}\r\n {2}",
                                         visitor.Translate(insert.Table),
                                         outputClause,
                                         visitor.Translate(insert.SelectQuery));
                }
            }
            else
            {
                if (insert.ColumnList != null)
                {
                    return string.Format("\r\nINSERT INTO {0} ({1}) {2}\r\n Values({3})",
                                         visitor.Translate(insert.Table),
                                         visitor.Translate(insert.ColumnList),
                                         outputClause,
                                         visitor.Translate(insert.InsertValues));
                }
                else
                {
                    return string.Format("\r\nINSERT INTO {0} {1}\r\n Values({2})",
                                         visitor.Translate(insert.Table),
                                         outputClause,
                                         visitor.Translate(insert.InsertValues));
                }
            }
        }

        protected static string ProcessInsertColumn(IQueryObject queryObject, MSSQL9Visitor visitor)
        {
            var column = queryObject as InsertColumn;
            var name = visitor.GetQueryObjectName(column).Replace("[", "").Replace("]", "");
            return string.Format("[{0}]", name);
        }

        protected static string ProcessLess(IQueryObject queryObject, MSSQL9Visitor visitor)
        {
            var less = queryObject as Less;
            var result = string.Format("{0} < {1}", visitor.Translate(less.Field), visitor.Translate(less.Than));
            return visitor.PrefixCriterion(less, result);
        }

        protected static string ProcessLessOrEqual(IQueryObject queryObject, MSSQL9Visitor visitor)
        {
            var less = queryObject as LessOrEqual;
            var result = string.Format("{0} <= {1}", visitor.Translate(less.Field), visitor.Translate(less.Than));
            return visitor.PrefixCriterion(less, result);
        }

        protected static string ProcessLike(IQueryObject queryObject, MSSQL9Visitor visitor)
        {
            var like = queryObject as Like;
            var result = string.Format("{0} like {1}", visitor.Translate(like.Field), visitor.Translate(like.Pattern));
            return visitor.PrefixCriterion(like, result);
        }
        
        protected static string ProcessLiteral(IQueryObject queryObject, MSSQL9Visitor visitor)
        {
            return (queryObject as Literal).Value.ToString();
        }

        protected static string ProcessNotEqual(IQueryObject queryObject, MSSQL9Visitor visitor)
        {
            var notEqual = queryObject as NotEqual;
            var result =
                (notEqual.To as Reference).Value.ToString() == "NULL" ?
                    string.Format("{0} IS NOT NULL", visitor.Translate(notEqual.Field)) :
                    string.Format("{0} <> {1}", visitor.Translate(notEqual.Field), visitor.Translate(notEqual.To));

            return visitor.PrefixCriterion(notEqual, result);
        }

        protected static string ProcessOrder(IQueryObject q, MSSQL9Visitor v)
        {
            var order = q as Order;

            if (order.ColumnList.Count == 0)
                return "";

            var builder = new DelimitedBuilder(" ");
            builder.AppendFormat(
                "\r\nORDER BY {0}",
                true,
                DelimitedBuilder.Construct(order.ColumnList.Select(c => v.Translate(c)), ", "));
            return builder.ToString();
        }

        protected static string ProcessOrderColumn(IQueryObject q, MSSQL9Visitor v)
        {
            var column = q as OrderColumn;
            var builder = new DelimitedBuilder("");
            builder.Append(v.GetQueryObjectName(column));

            if (column.Order != SortOrder.Unspecified)
            {
                builder.AppendFormat(" {0}", column.Order == SortOrder.Ascending ? "ASC" : "DESC");
            }
            return builder.ToString();
        }

        protected static string ProcessOutput(IQueryObject q, MSSQL9Visitor v)
        {
            var output = q as Output;

            var builder = new DelimitedBuilder(" ");
            builder.AppendFormat(
                "\r\nOUTPUT {0} INTO @{1}",
                true,
                DelimitedBuilder.Construct(output.ColumnList.Select(c => v.Translate(c)), ", "),
                output.Table.Name
                );

            return builder.ToString();
        }

        protected static string ProcessPage(IQueryObject q, MSSQL9Visitor v)
        {
            return "";
        }

        protected static string ProcessProperty(IQueryObject q, MSSQL9Visitor v)
        {
            return (q as Property);
        }

        protected static string ProcessQueryList(IQueryObject queryObject, MSSQL9Visitor visitor)
        {
            var list = queryObject as QueryList;
            var builder = new DelimitedBuilder(", ");

            if (list.Count > 0)
            {
                if (list.First() is ITableAction ||
                    list.First() is IQueryQualifier ||
                    list.First() is QueryList ||
                    list.First() is SearchCriteria ||
                    (list.First() is Variable && (list.First() as Variable).Value != null))
                    builder = new DelimitedBuilder(" ");

                list.ForEach(i => builder.Append(visitor.Translate(i)));
            }

            return builder.ToString();
        }

        protected static string ProcessRaiseError(IQueryObject queryObject, MSSQL9Visitor visitor)
        {
            var raiseError = queryObject as RaiseError;
            var result = string.Format("RAISERROR('{0}',{1},{2})",
                                       visitor.Translate(raiseError.ErrorMessage),
                                       visitor.Translate(raiseError.Severity),
                                       visitor.Translate(raiseError.ErrorCode));
            return result;
        }

        protected static string ProcessReference(IQueryObject q, MSSQL9Visitor v)
        {
            return (q as Reference).Value.ToString();
        }

        protected static string ProcessSchemaColumn(IQueryObject q, MSSQL9Visitor v)
        {
            var column = q as SchemaColumn;
            var builder = new DelimitedBuilder("");

            builder.AppendFormat("[{0}]", v.GetQueryObjectName(column).Replace("[", "").Replace("]", ""));
            builder.AppendFormat("    {0}", _provider.GetTypeDeclarationFromType(column.ColumnType));

            if (column.Identity)
                builder.Append(" IDENTITY(1,1)");

            if (column.PrimaryKey)
                builder.Append(" PRIMARY KEY");

            return builder.ToString();
        }

        protected static string ProcessSearchCriteria(IQueryObject q, MSSQL9Visitor v)
        {
            var criteria = q as SearchCriteria;
            criteria.First().Conjunction = Conjunctions.None;
            return DelimitedBuilder.Construct(criteria.Select(c => v.Translate(c)), " ");
        }

        protected static string ProcessSelect(IQueryObject q, MSSQL9Visitor v)
        {
            var builder = new DelimitedBuilder(" ");
            var select = q as Select;
            var page = v.GetPage(select);

            if (select.Alias != "")
                builder.Append("(");

            if (page != null)
            {
                select = v.PageSelect(select, page);
                builder.AppendFormat("\r\nSELECT {0} {1}", 
                    select.TopClause == null ? "" : v.Translate(select.TopClause),
                    v.Translate(select.ColumnList));
                builder.AppendFormat("\r\nFROM {0}", v.Translate(select.DerivedQuery));
                builder.Append(v.Translate(select.Qualifiers));
            }
            else
            {
                builder.AppendFormat("\r\nSELECT {0} {1}",
                    select.TopClause == null ? "" : v.Translate(select.TopClause),
                    v.Translate(select.ColumnList));
                builder.AppendFormat("\r\nFROM {0}", v.Translate(select.Table));
                builder.Append(v.Translate(select.Qualifiers));
            }

            if (select.Alias != "")
            {
                builder.AppendFormat(") {0}", select.Alias);
            }

            return builder.ToString();
        }

        protected static string ProcessTable(IQueryObject q, MSSQL9Visitor v)
        {
            var builder = new DelimitedBuilder(" ");
            var table = q as Table;

            if (table.JoinCriteria.Count > 0)
            {
                builder.AppendFormat("\r\n{0}", v._joinNames[table.JoinType]);
                if (table.Owner != "")
                    builder.AppendFormat("{0}.", table.Owner);

                builder.Append(table.IsVariable ? "@" + table.Name : table.Name);

                if (table.Alias != "")
                    builder.AppendFormat("{0}", table.Alias);

                builder.AppendFormat("ON {0}", v.Translate(table.JoinCriteria));
            }
            else
            {
                if (table.Owner != "")
                    builder.AppendFormat("{0}.", table.Owner);

                builder.Append(table.IsVariable ? "@" + table.Name : table.Name);

                if (table.Alias != "")
                    builder.AppendFormat("AS {0}", table.Alias);
            }

            table.JoinedTables.ForEach(j => builder.Append(v.Translate(j)));

            return builder.ToString();
        }

        protected static string ProcessTop(IQueryObject q, MSSQL9Visitor v)
        {
            var builder = new DelimitedBuilder(" ");
            var top = q as Top;
            
            builder.AppendFormat("TOP ({0})", v.Translate(top.Count));

            return builder.ToString();
        }
        
        protected static string ProcessTableVariable(IQueryObject q, MSSQL9Visitor v)
        {
            var variable = q as TableVariable;
            var builder = new DelimitedBuilder("");

            string columnList = v.Translate(variable.ColumnList);
            builder.AppendFormat("\r\nDECLARE @{0} TABLE ( {1} )",
                                 variable.Table.Name,
                                 columnList
                );
            return builder.ToString();
        }

        protected static string ProcessUpdate(IQueryObject queryObject, MSSQL9Visitor visitor)
        {
            var update = queryObject as Update;
            var builder = new DelimitedBuilder("");

            var outputClause = update.Output == null ?
                                "" : visitor.Translate(update.Output);

            builder.AppendFormat("\r\nUPDATE {0} SET {1}", update.Table.Name, visitor.Translate(update.ColumnList));
            builder.Append(outputClause);
            builder.Append(
                visitor.Translate(
                    update.Qualifiers.OfType<Where>().First()
                    )
                );

            return builder.ToString();
        }

        protected static string ProcessVariable(IQueryObject queryObject, MSSQL9Visitor visitor)
        {
            var variable = queryObject as Variable;
            var builder = new DelimitedBuilder("");

            if(variable.VariableType != null)
            {
                builder.AppendFormat("\r\nDECLARE @{0}      {1}", variable.Name, _provider.GetTypeDeclarationFromType(variable.VariableType));
            }
            else if(variable.Value != null)
            {
                builder.AppendFormat("\r\nSET @{0} = ({1})", variable.Name, visitor.Translate(variable.Value));
            }
            else
            {
                builder.AppendFormat("@{0}", variable.Name);
            }

            return builder.ToString();
        }

        protected static string ProcessWhere(IQueryObject q, MSSQL9Visitor v)
        {
            var whereClause = q as Where;

            if (whereClause.SearchCriteria.Count == 0)
                return "";

            string translation = v.Translate(whereClause.SearchCriteria);
            return string.Format("\r\nWHERE {0}", translation);
        }

        #endregion

    }
}